const { Pool } = require('pg');

// 从环境变量获取数据库配置
const pool = new Pool({
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD
});

/**
 * 获取所有菜品列表
 * @param {Object} filters - 过滤条件
 * @returns {Promise<Array>} 菜品列表
 */
async function getAllDishes(filters = {}) {
  const client = await pool.connect();
  try {
    let query = 'SELECT * FROM dishes WHERE 1=1';
    const params = [];
    let paramIndex = 1;

    // 默认只返回已上架菜品，除非明确指定 includeInactive 为 true
    if (filters.includeInactive !== true) {
      query += ` AND is_active = true`;
    }

    if (filters.category) {
      query += ` AND category = $${paramIndex}`;
      params.push(filters.category);
      paramIndex++;
    }

    if (filters.weatherCondition) {
      query += ` AND weather_condition = $${paramIndex}`;
      params.push(filters.weatherCondition);
      paramIndex++;
    }

    if (filters.search) {
      query += ` AND (name ILIKE $${paramIndex} OR description ILIKE $${paramIndex})`;
      params.push(`%${filters.search}%`);
      paramIndex++;
    }

    if (filters.sortBy === 'price_asc') {
      query += ' ORDER BY price ASC';
    } else if (filters.sortBy === 'price_desc') {
      query += ' ORDER BY price DESC';
    } else {
      query += ' ORDER BY created_at DESC';
    }

    const result = await client.query(query, params);
    return result.rows;
  } catch (error) {
    console.error('获取菜品列表失败:', error);
    throw error;
  } finally {
    client.release();
  }
}

/**
 * 根据ID获取菜品详情
 * @param {string} dishId - 菜品ID
 * @returns {Promise<Object>} 菜品详情
 */
async function getDishById(dishId) {
  const client = await pool.connect();
  try {
    const query = 'SELECT * FROM dishes WHERE id = $1';
    const result = await client.query(query, [dishId]);

    if (result.rows.length === 0) {
      throw new Error('菜品不存在');
    }

    return result.rows[0];
  } catch (error) {
    console.error('获取菜品详情失败:', error);
    throw error;
  } finally {
    client.release();
  }
}

/**
 * 根据天气条件获取推荐菜品
 * @param {string} weatherCondition - 天气状况
 * @param {number} temperature - 温度
 * @returns {Promise<Array>} 推荐菜品列表
 */
async function getRecommendedDishes(weatherCondition, temperature) {
  const client = await pool.connect();
  try {
    let weatherCategory = '';

    if (temperature <= 5) {
      weatherCategory = 'cold';
    } else if (temperature >= 28) {
      weatherCategory = 'hot';
    } else if (weatherCondition && weatherCondition.includes('雨')) {
      weatherCategory = 'rainy';
    } else if (weatherCondition && (weatherCondition.includes('晴') || weatherCondition.includes('多云'))) {
      weatherCategory = 'sunny';
    } else {
      weatherCategory = 'normal';
    }

    // 只返回已上架的推荐菜品
    const query = `
      SELECT * FROM dishes 
      WHERE weather_condition = $1 AND is_active = true
      ORDER BY RANDOM() 
      LIMIT 4
    `;

    const result = await client.query(query, [weatherCategory]);

    if (result.rows.length === 0) {
      // 如果没有匹配的天气推荐，则随机返回已上架菜品
      const fallbackQuery = 'SELECT * FROM dishes WHERE is_active = true ORDER BY RANDOM() LIMIT 4';
      const fallbackResult = await client.query(fallbackQuery);
      return fallbackResult.rows;
    }

    return result.rows;
  } catch (error) {
    console.error('获取推荐菜品失败:', error);
    throw error;
  } finally {
    client.release();
  }
}

/**
 * 创建新菜品
 * @param {Object} dishData - 菜品数据
 * @returns {Promise<Object>} 创建的菜品
 */
async function createDish(dishData) {
  console.log('[createDish] 接收到的数据:', JSON.stringify(dishData, null, 2));
  
  const { name, price, description, imageUrl, category, weatherCondition, recipe } = dishData;

  // 增强数据验证
  if (!name || typeof name !== 'string' || name.trim().length === 0) {
    console.error('[createDish] 验证失败: 菜品名称无效');
    throw new Error('菜品名称不能为空');
  }

  if (!price || isNaN(parseFloat(price)) || parseFloat(price) <= 0) {
    console.error('[createDish] 验证失败: 价格无效');
    throw new Error('价格必须是大于0的有效数字');
  }

  if (!category || typeof category !== 'string' || category.trim().length === 0) {
    console.error('[createDish] 验证失败: 菜品分类无效');
    throw new Error('菜品分类不能为空');
  }

  if (!description || typeof description !== 'string' || description.trim().length === 0) {
    console.error('[createDish] 验证失败: 菜品描述无效');
    throw new Error('菜品描述不能为空');
  }

  const client = await pool.connect();
  try {
    const query = `
      INSERT INTO dishes (name, price, description, image_url, category, weather_condition, recipe, is_active, created_at, updated_at)
      VALUES ($1, $2, $3, $4, $5, $6, $7, $8, NOW(), NOW())
      RETURNING *
    `;

    const values = [
      name.trim(),
      parseFloat(price),
      description.trim(),
      imageUrl || 'https://haisnap.tos-cn-beijing.volces.com/image/a8b861cb-e2ae-4085-b201-4fb302b0faf6_1760936728562.jpg',
      category.trim(),
      weatherCondition || 'normal',
      recipe ? recipe.trim() : '暂无制作步骤',
      true // 默认创建时为上架状态
    ];

    console.log('[createDish] 执行SQL插入，参数:', values);
    
    const result = await client.query(query, values);
    
    if (result.rows.length === 0) {
      console.error('[createDish] 插入失败：未返回数据');
      throw new Error('菜品创建失败：数据库未返回结果');
    }
    
    console.log('[createDish] ✓ 菜品创建成功:', result.rows[0].id, result.rows[0].name);
    
    return result.rows[0];
  } catch (error) {
    console.error('[createDish] 创建菜品失败:', error.message);
    console.error('[createDish] 错误堆栈:', error.stack);
    
    // 提供更友好的错误信息
    if (error.code === '23505') {
      throw new Error('菜品名称已存在，请使用其他名称');
    }
    
    throw error;
  } finally {
    client.release();
  }
}

/**
 * 更新菜品信息
 * @param {string} dishId - 菜品ID
 * @param {Object} updateData - 更新数据
 * @returns {Promise<Object>} 更新后的菜品
 */
async function updateDish(dishId, updateData) {
  const client = await pool.connect();
  try {
    const fields = [];
    const values = [];
    let paramIndex = 1;

    if (updateData.name !== undefined) {
      fields.push(`name = $${paramIndex}`);
      values.push(updateData.name.trim());
      paramIndex++;
    }

    if (updateData.price !== undefined) {
      fields.push(`price = $${paramIndex}`);
      values.push(parseFloat(updateData.price));
      paramIndex++;
    }

    if (updateData.description !== undefined) {
      fields.push(`description = $${paramIndex}`);
      values.push(updateData.description.trim());
      paramIndex++;
    }

    if (updateData.imageUrl !== undefined) {
      fields.push(`image_url = $${paramIndex}`);
      values.push(updateData.imageUrl);
      paramIndex++;
    }

    if (updateData.category !== undefined) {
      fields.push(`category = $${paramIndex}`);
      values.push(updateData.category.trim());
      paramIndex++;
    }

    if (updateData.weatherCondition !== undefined) {
      fields.push(`weather_condition = $${paramIndex}`);
      values.push(updateData.weatherCondition);
      paramIndex++;
    }

    if (updateData.recipe !== undefined) {
      fields.push(`recipe = $${paramIndex}`);
      values.push(updateData.recipe.trim());
      paramIndex++;
    }

    // 修复：正确处理 isActive 字段（支持布尔类型）
    if (updateData.isActive !== undefined) {
      fields.push(`is_active = $${paramIndex}`);
      values.push(Boolean(updateData.isActive));
      paramIndex++;
    }

    // 兼容旧版本：支持 is_active 字段名
    if (updateData.is_active !== undefined && updateData.isActive === undefined) {
      fields.push(`is_active = $${paramIndex}`);
      values.push(Boolean(updateData.is_active));
      paramIndex++;
    }

    if (fields.length === 0) {
      throw new Error('没有提供更新数据');
    }

    fields.push(`updated_at = NOW()`);
    values.push(dishId);

    const query = `
      UPDATE dishes 
      SET ${fields.join(', ')} 
      WHERE id = $${paramIndex} 
      RETURNING *
    `;

    const result = await client.query(query, values);

    if (result.rows.length === 0) {
      throw new Error('菜品不存在');
    }

    return result.rows[0];
  } catch (error) {
    console.error('更新菜品失败:', error);
    throw error;
  } finally {
    client.release();
  }
}

/**
 * 删除菜品
 * @param {string} dishId - 菜品ID
 * @returns {Promise<boolean>} 是否删除成功
 */
async function deleteDish(dishId) {
  const client = await pool.connect();
  try {
    const query = 'DELETE FROM dishes WHERE id = $1';
    const result = await client.query(query, [dishId]);
    return result.rowCount > 0;
  } catch (error) {
    console.error('删除菜品失败:', error);
    throw error;
  } finally {
    client.release();
  }
}

/**
 * 获取菜品分类列表
 * @returns {Promise<Array>} 分类列表
 */
async function getCategories() {
  const client = await pool.connect();
  try {
    // 只返回已上架菜品的分类
    const query = 'SELECT DISTINCT category FROM dishes WHERE category IS NOT NULL AND is_active = true ORDER BY category';
    const result = await client.query(query);
    return result.rows.map(row => row.category);
  } catch (error) {
    console.error('获取分类列表失败:', error);
    throw error;
  } finally {
    client.release();
  }
}

/**
 * 批量获取菜品（用于购物车）
 * @param {Array} dishIds - 菜品ID列表
 * @returns {Promise<Array>} 菜品列表
 */
async function getDishesByIds(dishIds) {
  if (!dishIds || dishIds.length === 0) {
    return [];
  }

  const client = await pool.connect();
  try {
    // 购物车查询不受上架状态限制，允许查看已加入购物车但后续下架的菜品
    const query = 'SELECT * FROM dishes WHERE id = ANY($1)';
    const result = await client.query(query, [dishIds]);
    return result.rows;
  } catch (error) {
    console.error('批量获取菜品失败:', error);
    throw error;
  } finally {
    client.release();
  }
}

/**
 * 搜索菜品
 * @param {string} keyword - 搜索关键词
 * @returns {Promise<Array>} 搜索结果
 */
async function searchDishes(keyword) {
  const client = await pool.connect();
  try {
    // 搜索时只返回已上架菜品
    const query = `
      SELECT * FROM dishes 
      WHERE (name ILIKE $1 OR description ILIKE $1) AND is_active = true
      ORDER BY name ASC
    `;
    const result = await client.query(query, [`%${keyword}%`]);
    return result.rows;
  } catch (error) {
    console.error('搜索菜品失败:', error);
    throw error;
  } finally {
    client.release();
  }
}

/**
 * 切换菜品上架状态（专用于上下架功能）
 * @param {string} dishId - 菜品ID
 * @param {boolean} newStatus - 新的上架状态
 * @returns {Promise<Object>} 更新后的菜品
 */
async function toggleDishStatus(dishId, newStatus) {
  const client = await pool.connect();
  try {
    console.log(`[toggleDishStatus] 开始更新菜品状态: dishId=${dishId}, newStatus=${newStatus}`);
    
    // 先检查菜品是否存在
    const checkQuery = 'SELECT id, name, is_active FROM dishes WHERE id = $1';
    const checkResult = await client.query(checkQuery, [dishId]);
    
    if (checkResult.rows.length === 0) {
      console.error(`[toggleDishStatus] 菜品不存在: dishId=${dishId}`);
      throw new Error('菜品不存在');
    }
    
    const oldStatus = checkResult.rows[0].is_active;
    console.log(`[toggleDishStatus] 菜品 "${checkResult.rows[0].name}" 当前状态: ${oldStatus}`);
    
    // 执行状态更新
    const updateQuery = `
      UPDATE dishes 
      SET is_active = $1, updated_at = NOW() 
      WHERE id = $2 
      RETURNING *
    `;
    
    const result = await client.query(updateQuery, [Boolean(newStatus), dishId]);
    
    if (result.rows.length === 0) {
      console.error(`[toggleDishStatus] 更新失败，未返回数据`);
      throw new Error('更新菜品状态失败');
    }
    
    console.log(`[toggleDishStatus] ✓ 状态更新成功: ${oldStatus} -> ${result.rows[0].is_active}`);
    
    return result.rows[0];
  } catch (error) {
    console.error('[toggleDishStatus] 切换菜品状态失败:', error.message);
    console.error('[toggleDishStatus] 错误堆栈:', error.stack);
    throw error;
  } finally {
    client.release();
  }
}

module.exports = {
  getAllDishes,
  getDishById,
  getRecommendedDishes,
  createDish,
  updateDish,
  deleteDish,
  getCategories,
  getDishesByIds,
  searchDishes,
  toggleDishStatus
};